package com.zx.oa.dao.ClassInfo;

import com.zx.oa.dao.BaseDao;
import com.zx.oa.dao.census.CensusDao;
import com.zx.oa.dao.census.CensusDaolpml;
import com.zx.oa.entity.CheckJob;
import com.zx.oa.entity.ClassInfo;
import com.zx.oa.entity.Major;
import com.zx.oa.entity.Student;


import javax.xml.crypto.Data;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ClassInfoDaoImpl extends BaseDao implements ClassInfoDao {

    Connection conn = null;

    PreparedStatement pstmt = null;

    ResultSet rs = null;

    @Override
    public List<ClassInfo> getClassInfo(String major) {
        List<ClassInfo> classInfoList = new ArrayList<ClassInfo>();
        StringBuffer sb = new StringBuffer("SELECT classId,className,major FROM `zteclassinfo` c,`ztemajor` m WHERE m.id=c.`studyType`");
        List<Object> params = new ArrayList<>();

        if (major != null && !"".equals(major)) {

            sb.append(" AND  `major` = ?");
            params.add(major);
        }

        conn = this.getConnection();
        try {
            pstmt = conn.prepareStatement(sb.toString());
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject((i + 1), params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("classId");
                String Name = rs.getString("className");
                String Major = rs.getString("major");
                ClassInfo classInfo = new ClassInfo(id, Name, Major);
                classInfoList.add(classInfo);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn, pstmt, rs);
        }
        return classInfoList;
    }

    @Override
    public boolean addClassInfo(String name, String major) {
        CensusDao censusDaolpml = new CensusDaolpml();
        List<Major> majorAll = censusDaolpml.getMajorAll();
        int Type = 0;
        for (Major majors : majorAll
        ) {
            if (majors.getMajor().equals(major) ) {
                Type = majors.getId();
            }
        }
        String sql = "INSERT INTO zteclassinfo(className,studyType) VALUES(?,?)";
        Object[] params = {name, Type};
        if (this.executeUpdateInfo(sql, params) > 0) {
            return true;
        }
        return false;
    }

    @Override
    public ClassInfo getClassInfo(int classId) {
        ClassInfo classInfo = null;
        String sb = "SELECT classId,className,major FROM `zteclassinfo` c,`ztemajor` m  WHERE m.id=c.`studyType` AND  `classId` = ?";





        conn = this.getConnection();
        try {
            pstmt = conn.prepareStatement(sb);

                pstmt.setObject(1,classId);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("classId");
                String Name = rs.getString("className");
                String Major = rs.getString("major");
                classInfo = new ClassInfo(id, Name, Major);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn, pstmt, rs);
        }
        return classInfo;
    }

    @Override
    public boolean modifyClassInfo(String name, String major, int classId) {
        CensusDao censusDaolpml = new CensusDaolpml();
        List<Major> majorAll = censusDaolpml.getMajorAll();
        int Type = 0;
        for (Major majors : majorAll
        ) {
            if (majors.getMajor() .equals(major) ) {
                Type = majors.getId();
            }
        }
        String sql = "UPDATE zteclassinfo SET className=?,studyType=? WHERE classid=?";

        Object[] params = {name, Type, classId};
        if (this.executeUpdateInfo(sql, params) > 0) {
            return true;
        }
        return false;
    }

    @Override
    public boolean deleteClassInfo(int id) {
        String sql = "DELETE FROM zteclassInfo WHERE classId=?";
        Object[] params = {id};

        if (this.executeUpdateInfo(sql, params) > 0) {
            return true;
        }
        return false;
    }
    @Override
    public boolean deleteCheckJobInfo(int id) {
        String sql = "DELETE FROM ztecheckjob WHERE id=?";
        Object[] params = {id};

        if (this.executeUpdateInfo(sql, params) > 0) {
            return true;
        }
        return false;
    }

    @Override
    public int getClassInfoTotalCount(String type) {
        int count=-1;//总记录数
        //可变长度字符串
        CensusDao censusDaolpml = new CensusDaolpml();
        List<Major> majorAll = censusDaolpml.getMajorAll();

        StringBuffer sb = new StringBuffer("select count(*) " +
                " FROM zteclassinfo c " +
                "LEFT JOIN ztemajor m ON c.studyType=m.id where 1=1 ");
        conn = this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object>params=new ArrayList<>();

        if(null!=type&&!"".equals(type)){
            sb.append(" AND m.`major`=?");
            params.add(type);
        }
        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                count=rs.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return count;
    }

    @Override
    public List<ClassInfo> getClassInfo(String type, int currentPageNo, int pageSize) {
        List<ClassInfo> classInfoList = new ArrayList<ClassInfo>();
        StringBuffer sb = new StringBuffer("SELECT classId,className,major FROM zteclassinfo c\n" +
                "LEFT JOIN ztemajor m ON c.studyType=m.id WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (type != null && !"".equals(type)) {

            sb.append(" AND  `major` = ?");
            params.add(type);
        }
        sb.append(" LIMIT ?,?");
        params.add((currentPageNo-1)*pageSize);
        params.add(pageSize);
        conn = this.getConnection();
        try {
            pstmt = conn.prepareStatement(sb.toString());
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject((i + 1), params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("classId");
                String Name = rs.getString("className");
                String Major = rs.getString("major");
                ClassInfo classInfo = new ClassInfo(id, Name, Major);
                classInfoList.add(classInfo);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn, pstmt, rs);
        }
        return classInfoList;
    }

    @Override
    public List<CheckJob> getClassInfo(String major, String className, String studentName, String date, String result, int currentPageNo, int pageSize) {
        List<CheckJob> checkJobList=new ArrayList<>();
        StringBuffer sb=new StringBuffer("SELECT j.id,s.`studentName`, j.ckTime,j.ckstatu FROM ztestudent s\n" +
                "INNER JOIN zteclassinfo c ON s.`classId`=c.`classId`\n" +
                "INNER JOIN ztemajor m ON c.`studyType`=m.`id`\n" +
                "LEFT JOIN ztecheckjob j ON j.studentNo = s.`studentId`\n" +
                "WHERE 1=1");
        conn = this.getConnection();

        List<Object> params=new ArrayList<>();
        if (null!=major&&!"".equals(major)){
            sb.append(" AND m.`major`=?");
            params.add(major);
        }
        if (null!=className&&!"".equals(className)){
            sb.append(" AND c.`className`=?");
            params.add(className);
        }
        if (null!=studentName&&!"".equals(studentName)){
            sb.append(" AND s.`studentName`=?");
            params.add(studentName);
        }
        if (null!=date&&!"".equals(date)){
            sb.append(" AND j.`ckTime` LIKE ?");
            params.add("%"+date+"%");
        }
        if (null!=result&&!"".equals(result)){
            sb.append(" AND j.ckstatu=?");
            params.add(result);
        }
        sb.append(" ORDER BY id DESC");
        sb.append(" LIMIT ?,? ");
        params.add((currentPageNo-1)*pageSize);
        params.add(pageSize);
        try {
            pstmt=conn.prepareStatement(sb.toString());
            for (int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String studentName1 = rs.getString("studentName");
                Date ckTime = rs.getDate("ckTime");
                String ckstatu = rs.getString("ckstatu");

                CheckJob checkJob=new CheckJob(id,studentName1, ckTime,ckstatu);


                checkJobList.add(checkJob);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return checkJobList;
    }

    @Override
    public List<CheckJob> getClassInfo(String major, String className, String month) {
        List<CheckJob> checkJobList=new ArrayList<>();
        StringBuffer sb=new StringBuffer("SELECT j.id,s.`studentName`,j.ckTime,j.ckstatu FROM ztestudent s\n" +
                "INNER JOIN zteclassinfo c ON s.`classId`=c.`classId`\n" +
                "INNER JOIN ztemajor m ON c.`studyType`=m.`id`\n" +
                "LEFT JOIN ztecheckjob j ON j.studentNo = s.`studentId`\n" +
                "WHERE 1=1");
        conn = this.getConnection();

        List<Object> params=new ArrayList<>();
        if (null!=major&&!"".equals(major)){
            sb.append(" AND m.`major`=?");
            params.add(major);
        }
        if (null!=className&&!"".equals(className)){
            sb.append(" AND c.`className`=?");
            params.add(className);
        }
        if (null!=month&&!"".equals(month)){
            sb.append(" AND j.`ckTime` LIKE ?");
            params.add("%"+month+"%");
        }
        sb.append(" ORDER BY id DESC");

        try {
            pstmt=conn.prepareStatement(sb.toString());
            for (int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String studentName1 = rs.getString("studentName");
                Date ckTime = (Date) rs.getObject("ckTime");
                String ckstatu = rs.getString("ckstatu");

                CheckJob checkJob=new CheckJob(id,studentName1, ckTime,ckstatu);


                checkJobList.add(checkJob);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return checkJobList;
    }

    @Override
    public List<ClassInfo> getClassInfoByStudyType(String major) {
        List<ClassInfo> classInfoList=new ArrayList<>();
        conn=this.getConnection();
        String sql="SELECT cla.* FROM zteclassinfo cla,ztemajor maj\n" +
                "WHERE cla.`studyType`=maj.`id`\n" +
                "AND maj.`major`=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,major);
            rs=pstmt.executeQuery();
            while (rs.next()){
                Integer id=rs.getInt(1);
                String name=rs.getString(2);
                Integer type=rs.getInt(3);
                ClassInfo classInfo=new ClassInfo(id,name,type);
                classInfoList.add(classInfo);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,pstmt,rs);
        }
        return classInfoList;
    }

    @Override
    public List<Major> getMajorAll() {
        List<Major> majorList=new ArrayList<>();
        String sql="SELECT id,major FROM ztemajor";
        conn= this.getConnection();
        try {
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while (rs.next()){
                int id= rs.getInt("id");
                String major=  rs.getString("major");
                Major m=new Major(id,major);
                majorList.add(m);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return majorList;
    }

    @Override
    public List<ClassInfo> getClassAll(String majorName) {
        List<ClassInfo> classInfoList=new ArrayList<>();
        StringBuffer sb=new StringBuffer("SELECT classId,className,studyType FROM zteclassinfo c \n" +
                "INNER JOIN ztemajor m ON m.`id`=c.`studyType`");
        if (null!=majorName||!"".equals(majorName)){
            sb.append(" WHERE m.`major` = ? ");
        }
        conn= this.getConnection();
        try {
            pstmt=conn.prepareStatement(sb.toString());
            if (null!=majorName||!"".equals(majorName)){
                pstmt.setObject(1,majorName);
            }
            rs=pstmt.executeQuery();
            while (rs.next()){
                int classId= rs.getInt("classId");
                String className=  rs.getString("className");
                int  studyType=  rs.getInt("studyType");

                ClassInfo classInfo=new ClassInfo(classId,className,studyType);
                classInfoList.add(classInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return classInfoList;
    }

    @Override
    public List<CheckJob> getJob() {
        List<CheckJob> checkJobList=new ArrayList<>();
        String sql="SELECT id,studentNo,ckTime,ckstatu FROM ztecheckjob";
        conn= this.getConnection();
        try {
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                int studentNo = rs.getInt("studentNo");
                Date ckTime= rs.getDate("ckTime");
                String ckstatu = rs.getString("ckstatu");

                CheckJob checkJob=new CheckJob(id,studentNo,ckTime,ckstatu);

                checkJobList.add(checkJob);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return checkJobList;
    }

    @Override
    public boolean updateJob(int id, String cktime, String ckstatu) {
        String sql = "UPDATE ztecheckjob SET ckTime=?,ckstatu=? WHERE id= ?";
        Object[] params = {cktime,ckstatu,id};

        if (this.executeUpdateInfo(sql, params) > 0) {
            return true;
        }
        return false;
    }

    @Override
    public int getClassTotalCount(String major, String className, String studentName, String date, String result) {
        int count=-1;
        StringBuffer sb=new StringBuffer("SELECT COUNT(*) FROM ztestudent s\n" +
                "INNER JOIN zteclassinfo c ON s.`classId`=c.`classId`\n" +
                "INNER JOIN ztemajor m ON c.`studyType`=m.`id`\n" +
                "LEFT JOIN ztecheckjob j ON j.studentNo = s.`studentId`\n" +
                "WHERE 1=1");
        conn = this.getConnection();

        List<Object> params=new ArrayList<>();
        if (null!=major&&!"".equals(major)){
            sb.append(" AND m.`major`=?");
            params.add(major);
        }
        if (null!=className&&!"".equals(className)){
            sb.append(" AND c.`className`=?");
            params.add(className);
        }
        if (null!=studentName&&!"".equals(studentName)){
            sb.append(" AND s.`studentName`=?");
            params.add(studentName);
        }
        if (null!=date&&!"".equals(date)){
            sb.append(" AND j.`ckTime` LIKE ?");
            params.add("%"+date+"%");
        }
        if (null!=result&&!"".equals(result)){
            sb.append(" AND j.ckstatu=?");
            params.add(result);
        }
        try {
            pstmt=conn.prepareStatement(sb.toString());
            for (int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return count;
    }
}